package com.scalar.chiptrack.operations.process.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.dataentry.LotDetails;
import com.scalar.chiptrack.operations.process.ProductChange;
import com.scalar.chiptrack.operations.process.BomPartNumbersAndQuantity;
import com.scalar.chiptrack.operations.reports.TransactionDetail;
import com.scalar.chiptrack.operations.reports.forms.DetailedTransactionReportForm;
import com.scalar.chiptrack.utils.ExcelUtils;

/**
 * User: KP
 * Date: Dec 27, 2006
 * Time: 5:02:52 PM
  */
public class NewProductDAO implements Constants
{
    public NewProductDAO() { }
        
    public static ArrayList<LabelValueBean> loadAllProcessesFromDB(DBTransaction dbTransaction, boolean isShowDefalutLabel)  throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE ORDER BY SORTING_ORDER");
            rs = pstmt.executeQuery();
            if (isShowDefalutLabel)
            processList.add( new LabelValueBean( "All Process Steps", "0" ) );
            while (rs.next())
            {
                processList.add( new LabelValueBean( rs.getString( "PROCESS_TYPE"),  rs.getString( "PROCESS_TYPE" ) ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processList;

    }
    public static String loadAllProcessesStringFromDB(DBTransaction dbTransaction)  throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String psTyepStr        = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE ORDER BY SORTING_ORDER");
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                if ( psTyepStr != null )
                {
                     psTyepStr = psTyepStr +","+rs.getString( "PROCESS_TYPE");
                }
                else
                {
                    psTyepStr = rs.getString( "PROCESS_TYPE");
                }
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return psTyepStr;

    }
    public static ArrayList<LabelValueBean> loadWipProcessesFromDB(DBTransaction dbTransaction, boolean isShowDefalutLabel) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE Is_Wip = 0 ORDER BY SORTING_ORDER");
            rs = pstmt.executeQuery();
            if (isShowDefalutLabel)
            processList.add( new LabelValueBean( "All Process Steps", "0" ) );
            while (rs.next())
            {
                processList.add( new LabelValueBean( rs.getString( "PROCESS_TYPE"),  rs.getString( "PROCESS_TYPE" ) ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all WIP Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processList;

    }

    public static String getBaseProcessName(DBTransaction dbTransaction, String psType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String baseProcessType  = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ?");
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                baseProcessType = rs.getString("BASE_PROCESS_TYPE");
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Base Process information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return baseProcessType;

    }

    public static String getNextPsType(DBTransaction dbTransaction, String psType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String processType  = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER = (SELECT SORTING_ORDER+1 FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? )");
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                processType = rs.getString("PROCESS_TYPE");
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load Next Process Type information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processType;

    }

    public static String getNextPsTypeName(DBTransaction dbTransaction, String psType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String baseProcessType  = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER = (SELECT SORTING_ORDER+1 FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? )");
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                baseProcessType = rs.getString("BASE_PROCESS_TYPE");
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load Next Process Type information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return baseProcessType;

    }

    public static String getPreviousPsType(DBTransaction dbTransaction, String psType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String processType  = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER = (SELECT SORTING_ORDER-1 FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? )");
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                processType = rs.getString("PROCESS_TYPE");
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Base Process information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processType;
    }

    public static String getPreviousPsTypeName(DBTransaction dbTransaction, String psType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String baseProcessType  = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER = (SELECT SORTING_ORDER-1 FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? )");
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                baseProcessType = rs.getString("BASE_PROCESS_TYPE");
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Base Process information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return baseProcessType;

    }


    public static ArrayList<LabelValueBean> loadAllNextPsTypes(DBTransaction dbTransaction, String psType, boolean includePsType ) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList <LabelValueBean> nextProcessList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            String query = null;
            if (includePsType)
            {
                query = "SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER >= (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? ) Order by SORTING_ORDER";
            }
            else
            {
                query = "SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER > (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? ) Order by SORTING_ORDER";
            }
            pstmt = conn.prepareStatement(query );
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                nextProcessList.add( new LabelValueBean(rs.getString("PROCESS_TYPE"), rs.getString("PROCESS_TYPE") ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Base Process information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return nextProcessList;

    }

    public static ArrayList<LabelValueBean> loadAllPreviousPsTypes(DBTransaction dbTransaction, String psType, boolean includePsType) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList <LabelValueBean> nextProcessList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            String query = null;
            if (includePsType)
            {
                query = "SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER <= (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? ) Order by SORTING_ORDER";
            }
            else
            {
                query = "SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER < (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = ? ) Order by SORTING_ORDER";
            }
            pstmt = conn.prepareStatement(query );
            pstmt.setString( 1, psType );
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                nextProcessList.add( new LabelValueBean(rs.getString("PROCESS_TYPE"), rs.getString("PROCESS_TYPE") ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Base Process information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return nextProcessList;

    }

    public static ArrayList<LabelValueBean> loadAllProcesses(DBTransaction dbTransaction)  throws Exception
    {
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();
        
        processList.add( new LabelValueBean( "All Process Steps", "0" ) );
        processList.add( new LabelValueBean( "FAB", "Fab" ) );
        processList.add( new LabelValueBean( "FAB OPTION", "Fab Option" ) );
        processList.add( new LabelValueBean( "WAFER STORE", "Wafer Bank" ) );
        processList.add( new LabelValueBean( "DIE BANK", "Die Bank" ) );
        processList.add( new LabelValueBean( "ASSEMBLY", "Assembly" ) );
        processList.add( new LabelValueBean( "FPO", "FPO" ) );
        processList.add( new LabelValueBean( "FT", "FT" ) );
        processList.add( new LabelValueBean( "CLASS STORE", "Tape & Reel" ) );
        processList.add( new LabelValueBean( "FINISHED GOODS", "FG" ) );

        return processList;
    }

    public static ArrayList<LabelValueBean> loadWipProcesses(DBTransaction dbTransaction) throws Exception
    {
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();

        processList.add( new LabelValueBean( "All Process Steps", "0" ) );
        processList.add( new LabelValueBean( "FAB", "Fab" ) );
        processList.add( new LabelValueBean( "FAB OPTION", "Fab Option" ) );
        processList.add( new LabelValueBean( "ASSEMBLY", "Assembly" ) );
        processList.add( new LabelValueBean( "FPO", "FPO" ) );
        processList.add( new LabelValueBean( "FT", "FT" ) );

        return processList;
    }
    public static ArrayList<LabelValueBean> loadInventoryProcesses(DBTransaction dbTransaction) throws Exception
    {
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();

        processList.add( new LabelValueBean( "All Process Steps", "0" ) );
        processList.add( new LabelValueBean( "WAFER STORE", "Wafer Bank" ) );
        processList.add( new LabelValueBean( "DIE BANK", "Die Bank" ) );
        processList.add( new LabelValueBean( "CLASS STORE", "Tape & Reel" ) );
        processList.add( new LabelValueBean( "FINISHED GOODS", "FG" ) );

        return processList;
    }

    public static ArrayList<LabelValueBean> loadInventoryProcessesFromDB(DBTransaction dbTransaction) throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE Is_Wip = 1 ORDER BY SORTING_ORDER");
            rs = pstmt.executeQuery();
            processList.add( new LabelValueBean( "All Process Steps", "0" ) );
            while (rs.next())
            {
                processList.add( new LabelValueBean( rs.getString( "PROCESS_TYPE"),  rs.getString( "PROCESS_TYPE" ) ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Inventory Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processList;
    }


    public static ArrayList<LabelValueBean> loadTransitProcessesFromDB(DBTransaction dbTransaction, boolean isAllOptionRequired, boolean isIncludeFG)  throws Exception
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList<LabelValueBean> processList = new ArrayList<LabelValueBean>();
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE Is_Wip = 0 ORDER BY SORTING_ORDER");
            rs = pstmt.executeQuery();

            if (isAllOptionRequired )
            {
                processList.add( new LabelValueBean( "All Process Steps", "0" ) );
            }
            while (rs.next())
            {
                processList.add( new LabelValueBean( rs.getString( "PROCESS_TYPE"),  rs.getString( "PROCESS_TYPE" ) ) );
            }
            if( isIncludeFG )
            {
                processList.add(new LabelValueBean( "FG",  "FG" ) );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Transit Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return processList;
    }
    /**
     *  This method loads lot status i,e Running or Hold
     * @return lotStatusList
     */
    public static ArrayList<LabelValueBean> loadStatusList()
    {
        ArrayList<LabelValueBean> statusList = new ArrayList<LabelValueBean>();
        statusList.add( new LabelValueBean( Constants.LOT_STATUS_RUNNING, Constants.LOT_STATUS_RUNNING) );
        statusList.add( new LabelValueBean( Constants.LOT_STATUS_HOLD, Constants.LOT_STATUS_HOLD ) );
        return statusList;
    }

    /**
     *  This method loads lot Category i,e Prod,Engg,Rma
     * @return lot categoryList
     */
    public static ArrayList<LabelValueBean> loadCategoryList()
    {
        ArrayList<LabelValueBean> categoryList = new ArrayList<LabelValueBean>();
        categoryList.add( new LabelValueBean(AdaptorConstants.LOT_CATEGORY_TYPE_PROD , AdaptorConstants.LOT_CATEGORY_TYPE_PROD) );
        categoryList.add( new LabelValueBean( AdaptorConstants.LOT_CATEGORY_TYPE_ENGG, AdaptorConstants.LOT_CATEGORY_TYPE_ENGG ) );
        categoryList.add( new LabelValueBean( AdaptorConstants.LOT_CATEGORY_TYPE_RMA, AdaptorConstants.LOT_CATEGORY_TYPE_RMA ) );
        return categoryList;
    }
    /**
     * This method loads Base Product depending on various conditions
     * @param dbTransaction
     * @param chipBaseProductsOnly
     * @param withOutObsoleteProducts
     * @return baseProductsList
     * @throws ScalarException
     */
    public static ArrayList<LabelValueBean> loadAllBaseProducts( DBTransaction dbTransaction, boolean chipBaseProductsOnly, boolean withOutObsoleteProducts ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement ps    = null;
        ResultSet rs            = null;
        ArrayList<LabelValueBean> list          = new ArrayList<LabelValueBean>();
        String partNumber;
        String productId;
        String queryString;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( withOutObsoleteProducts )
            {
                if( chipBaseProductsOnly )
                {
                    queryString = "SELECT PRODUCT_ID, PART_NUMBER, P_TYPE, P_COMMENTS, OBSOLETE FROM FN_LOAD_ALL_BASE_PRODUCTS() WHERE OBSOLETE <> 0 AND P_TYPE = 'DIE' ";
                }
                else
                {
                    queryString = "SELECT PRODUCT_ID, PART_NUMBER, P_TYPE, P_COMMENTS, OBSOLETE FROM FN_LOAD_ALL_BASE_PRODUCTS() WHERE OBSOLETE <> 0";
                }
            }
            else
            {
                if( chipBaseProductsOnly )
                {
                    queryString = "SELECT PRODUCT_ID, PART_NUMBER, P_TYPE, P_COMMENTS, OBSOLETE FROM FN_LOAD_ALL_BASE_PRODUCTS() WHERE P_TYPE = 'DIE'";
                }
                else
                {
                    queryString = "SELECT PRODUCT_ID, PART_NUMBER, P_TYPE, P_COMMENTS, OBSOLETE FROM FN_LOAD_ALL_BASE_PRODUCTS()";
                }
            }

            ps = conn.prepareStatement( queryString );
            rs = ps.executeQuery();

            while (rs.next())
            {
                productId   = rs.getString( "PRODUCT_ID" );
                partNumber  = rs.getString( "PART_NUMBER" );

                list.add( new LabelValueBean( partNumber,  productId ) );
            }

        }
        catch (SQLException se)
        {
            se.printStackTrace();
            throw new ScalarException( "Could not load all base products information ", se );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( ps != null )
            {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }


    /**
     * This method loads Part Numbers depending on various conditions
     * @param dbTransaction
     * @param productType i,e Whether 0 (ALL) ( DIE+BOARD+KIT ) or 1 (ONLY DIE's) or 2 (ONLY BOM's)
     * @param processType i,e Whether 0 (ALL PROCESS) or PARTICULAR PROCESS
     * @param withOutObsoleteProducts i,e Whether 0 (ALL PROCESS) or PARTICULAR PROCESS
     * @param returnWithLabelValueBean
     * @param defaultProcessStepId
     * @return partNumbersList
     * @throws ScalarException
     */
    public static ArrayList loadPartNumbers ( DBTransaction dbTransaction, int productType, String processType, boolean withOutObsoleteProducts, boolean returnWithLabelValueBean, boolean defaultProcessStepId, int parentProductId ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList partNumberList    = null;
        PartNumber partNumber       = null;

        try
        {
            //System.out.println("    productType = " + productType + "    processType = " + processType + "    withOutObsoleteProducts = " + withOutObsoleteProducts + "    returnWithLabelValueBean = " + returnWithLabelValueBean + "    defaultProcessStepId = " + defaultProcessStepId );

            partNumberList = new ArrayList();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "{CALL SP_LOAD_ALL_PART_NUMBERS_BY_PROCESS( ?, ?, ?, ? ) }" );

            pstmt.setString ( 1, processType);                                  // HERE 0 MEANS ALL PROCESS PRODUCTS OTHERWISE FETCH ONLY PARTICULAR PROCESS PRODUCTS
            pstmt.setInt    ( 2, productType);                                  // HERE 0 = ALL( DIE+BOARD+KIT ), 1 = ONLY DIE , 2 = ONLY BOM
            pstmt.setInt    ( 3, ( withOutObsoleteProducts ? 0 : 1 ) );         // HERE 0 NOT OBSOLETE PART NO'S ONLY OTHERWISE LOAD ALL TYPES OF PART NO'S
            pstmt.setInt    ( 4, parentProductId );                             // HERE 0 MEANS ALL BASE PRODUCTS OTHERWISE FETCH ONLY GIVEN BASE PRODUCT PART NUMBERS

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                if( returnWithLabelValueBean == false )
                {
                    partNumber = new PartNumber();

                    partNumber.setProcessType       ( rs.getString("PS_TYPE") );
                    partNumber.setProcessTypeName   ( rs.getString("PS_TYPE_NAME") );
                    partNumber.setPartNumber        ( rs.getString("PART_NUMBER") );
                    partNumber.setProcessStepId     ( rs.getString("PROCESSSTEP_ID") );
                    partNumber.setProductId         ( rs.getString("PRODUCT_ID") );

                    partNumberList.add( partNumber );
                }
                else
                {
                    if( defaultProcessStepId )
                    {
                        partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
                    }
                    else
                    {
                        partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PRODUCT_ID" ) ) );
                    }
                }
            }

        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Part Numbers by type", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumberList;
    }

    /**
     * This method loads Shop Order Part Numbers
     * @param dbTransaction
     * @return shopOrderPartNumbersList
     * @throws ScalarException
     */
    public static ArrayList<LabelValueBean> loadSOPartNumbers( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> partNumberList    = null;
        String partNumber 			= null;
        String processStepId        = null;

        try
        {
            partNumberList = new ArrayList<LabelValueBean>();

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT SO_PART_NUMBER, P.PROCESSSTEP_ID FROM PO_TABLE PT, PRODUCT P, PROCESS_STEP PS WHERE PT.SO_PART_NUMBER = P.PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE IN ( 'Fab', 'BUMPING', 'CP', 'BACK GRIND', 'ASSEMBLY' )" );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                partNumber = rs.getString( "SO_PART_NUMBER" );
                processStepId = rs.getString( "PROCESSSTEP_ID" );
                partNumberList.add( new LabelValueBean( partNumber, processStepId ) );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number ", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumberList;
    }

    public static ArrayList<LabelValueBean> loadAssemblyReleaseProcessParameters( DBTransaction dbTransaction, int processstepId ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> parameterList     = null;
        String parameterKey 		= null;
        String parameterValue       = null;

        try
        {
            parameterList = new ArrayList<LabelValueBean>();

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT [PARAMETER_KEY], [PARAMETER_VALUE] FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = ? AND PARAMETER_KEY IN ( 'Die Size', 'Bond Pad Open Area', 'Top Metal','Back Metal','Wafer Thickness', 'Wafer Diameter', 'Wire Size', 'Delta Vsd Program', 'Qrr Test Program')" );
            pstmt.setInt ( 1, processstepId );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                parameterKey = rs.getString( "PARAMETER_KEY" );
                parameterValue = rs.getString( "PARAMETER_VALUE" );
                parameterList.add( new LabelValueBean( parameterKey, parameterValue ) );
                //System.out.println( new LabelValueBean( parameterKey, parameterValue ) );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number ", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return parameterList;
    }

    /**
     * This method is used to load distinct parameter values based on the parameter key
     * @param dbTransaction
     * @param parameterKey
     * @return
     * @throws ScalarException
     */
    public static ArrayList<LabelValueBean> loadProcessParametersByType ( DBTransaction dbTransaction, String parameterKey  ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> processParameterList    = null;

        try
        {
            processParameterList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ? and len(PARAMETER_VALUE) > 0" );

            pstmt.setString ( 1, parameterKey);

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                     processParameterList.add( new LabelValueBean( rs.getString( "PARAMETER_VALUE" ), rs.getString( "PARAMETER_VALUE" ) ) );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not load Process Parameters", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return processParameterList;
    }

    /**
     * This method loads the products details used for Product Detail Report.
     * @param dbTransaction
     * @param partNumber
     * @param packageType
     * @param pinCount
     * @param productGroup
     * @param defaultPrompt
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadProductDetails ( DBTransaction dbTransaction, String partNumber,String packageType,String pinCount,String productGroup,String defaultPrompt) throws ScalarException
    {
         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList productDetaislList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL SP_LOAD_PRODUCT_DETAILS( ?, ?, ?, ?, ? ) }" );
             pstmt.setString( 1, partNumber );
             pstmt.setString( 2, packageType );
             pstmt.setString( 3, pinCount );
             pstmt.setString( 4, productGroup );
             pstmt.setString( 5, defaultPrompt );

             rset    = pstmt.executeQuery();

              productDetaislList =ExcelUtils.getAllResultSetRows(rset, true);

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Product Details", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return productDetaislList;
    }

    /**
     * This method loads the MKT Part Number this need to modified to load product id and opt part numbner based on the parameter passed.
     * @param dbTransaction
     * @param processType
     * @return
     * @throws ScalarException
     */
    public static ArrayList<LabelValueBean> loadMarketingPartNumbers ( DBTransaction dbTransaction,String processType ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> mktPartNumberList    = null;

        try
        {
            mktPartNumberList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT P_MARKETING_PART_NUMBER,P.PROCESSSTEP_ID FROM PRODUCT P,PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = ? and len(P_MARKETING_PART_NUMBER) > 0 " );

            pstmt.setString ( 1, processType);

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                     mktPartNumberList.add( new LabelValueBean( rs.getString( "P_MARKETING_PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not load Process Parameters", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return mktPartNumberList;
    }

    public static BomPartNumbersAndQuantity getBomPartNumber( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection conn 			= null;
        PreparedStatement pstmt 	= null;
        ResultSet rs 				= null;
        BomPartNumbersAndQuantity bomPartNumbersAndQuantity = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            if( processStepId > 0 )
            {
                pstmt 	= conn.prepareStatement( "select bom_processStep_id, part_number, qty from assembly_bom_setup_table abst, product p where assembly_processstep_id = ? and bom_processstep_id = processstep_id" );
                pstmt.setInt( 1, processStepId );
                rs 		= pstmt.executeQuery();

                if( rs.next() )
                {
                    bomPartNumbersAndQuantity = new BomPartNumbersAndQuantity();
                    bomPartNumbersAndQuantity.setBomProcessStepId( rs.getInt ( "bom_processStep_id" ) );
                    bomPartNumbersAndQuantity.setBomPartNumber( rs.getString( "part_number" ) );
                    bomPartNumbersAndQuantity.setQuantity( rs.getInt ( "qty" ) + "" );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not fetch the BOM Part Number for the Assembly Part Number.");
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return bomPartNumbersAndQuantity;
    }

    public static String getProcessParameterValue( DBTransaction dbTransaction, int processstepId, String parameterKey ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        String parameterValue       = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = ? AND PARAMETER_KEY = ?" );
            pstmt.setInt    ( 1, processstepId );
            pstmt.setString ( 2, parameterKey );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                parameterValue = rs.getString( "PARAMETER_VALUE" );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number Parameter value", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return parameterValue;
    }
    public static ArrayList<LabelValueBean> getProcessParameterValueList( DBTransaction dbTransaction, String parameterKey ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> parameterValueList = new ArrayList<LabelValueBean>();

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ? AND PARAMETER_VALUE IS NOT NULL AND LEN(PARAMETER_VALUE) > 0 " );
            pstmt.setString ( 1, parameterKey );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                parameterValueList.add( new LabelValueBean( rs.getString( "PARAMETER_VALUE" ), rs.getString( "PARAMETER_VALUE" ) ) );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Process Parmeter value Parameter value", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return parameterValueList;
    }
    public static int getForwardOrBackwardProcessstepId( DBTransaction dbTransaction, int processstepId, int fowardOrBackward, String forwardProcessType ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        int forwardProcessstepId    = -1;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( ?, ? ) WHERE PS_TYPE = ? " );
            pstmt.setInt    ( 1, processstepId );
            pstmt.setInt    ( 2, fowardOrBackward );
            pstmt.setString ( 3, forwardProcessType );

            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                forwardProcessstepId = rs.getInt( "PROCESSSTEP_ID" );
            }

        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number ", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return forwardProcessstepId;
    }

    public static ArrayList<LabelValueBean> loadForwardOrBackwardPartNumbers( DBTransaction dbTransaction , int sourcePsStepId, int forwardOrBackward, int flowPsStepId, String flowpsType, boolean includeFlowPartNo) throws Exception
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> partNumberList    = null;

        try
        {
            partNumberList = new ArrayList<LabelValueBean>();

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PROCESSSTEP_ID , Product_name FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( ?, ? ) WHERE PS_TYPE = ?" );
            pstmt.setInt ( 1, sourcePsStepId );
            pstmt.setInt ( 2, forwardOrBackward );
            pstmt.setString ( 3, flowpsType );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                if ( includeFlowPartNo )
                {
                    partNumberList.add( new LabelValueBean( rs.getString( "Product_name" ), rs.getString( "PROCESSSTEP_ID" ) ) );
                }
                else
                {
                    if (!rs.getString("PROCESSSTEP_ID").equalsIgnoreCase(flowPsStepId + ""))
                    {
                        partNumberList.add( new LabelValueBean( rs.getString( "Product_name" ), rs.getString( "PROCESSSTEP_ID" ) ) );
                    }
                }
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not Load Part Numbers list", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumberList;

    }
    public static ArrayList<LabelValueBean> loadLikePartsByPartNumber( DBTransaction dbTransaction ,String partNo, String likePart, String psType, boolean  includePartNo ) throws Exception
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> partNumberList    = null;

        try
        {
            partNumberList = new ArrayList<LabelValueBean>();

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT P.PROCESSSTEP_ID , PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS  WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PART_NUMBER like ? AND PS_TYPE =?" );
            pstmt.setString ( 1, likePart + '%' );
            pstmt.setString ( 2, psType );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                if ( includePartNo )
                {
                    partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
                }
                else
                {
                    if (!rs.getString("PART_NUMBER").equalsIgnoreCase(partNo))
                    {                           
                        partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
                    }
                }
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not Load Part Numbers list", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumberList;

    }
    public static String loadPartNumber( DBTransaction dbTransaction, int processstepId, boolean returnMktPartNoIfFG, boolean neededFowardOrBackwardFlowPartNo, int fowardOrBackward, String forwardOrBackwardProcessType  ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        String partNumber           = null;

        try
        {
            if( neededFowardOrBackwardFlowPartNo )
            {
                processstepId = getForwardOrBackwardProcessstepId ( dbTransaction,  processstepId, fowardOrBackward, forwardOrBackwardProcessType );
            }
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT PART_NUMBER, P_MARKETING_PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = ? " );
            pstmt.setInt    ( 1, processstepId );

            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                partNumber = ( returnMktPartNoIfFG == false ? rs.getString( "PART_NUMBER" ) : rs.getString( "P_MARKETING_PART_NUMBER" ) ) ;
            }

        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number ", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumber;
    }
    
    public static ArrayList<LabelValueBean> getPartNumberbyProductType( DBTransaction dbTransaction, String productType, String isboardorkit) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> partNumberList    = null;

        try
        {
            partNumberList = new ArrayList<LabelValueBean>();
            
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "{CALL sp_get_board_kit_parts_by_ps_type( ?, ?) }" );
            pstmt.setString ( 1, productType );
            pstmt.setString ( 2, isboardorkit );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not Load Board/Kit Part Number value", sql);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return partNumberList;
    }

    public static ArrayList<LabelValueBean> loadMarketingPartNumbersbyProductID ( DBTransaction dbTransaction,String processType,String productId ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> mktPartNumberList    = null;

        try
        {
            mktPartNumberList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if ( productId != null && Integer.parseInt(productId) > 0 )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT P_MARKETING_PART_NUMBER,P.PROCESSSTEP_ID FROM PRODUCT P,PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = ? and len(P_MARKETING_PART_NUMBER) > 0 and p_parent_product_id = ? " );
                pstmt.setString ( 1, processType);
                pstmt.setString ( 2, productId );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT P_MARKETING_PART_NUMBER,P.PROCESSSTEP_ID FROM PRODUCT P,PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = ? and len(P_MARKETING_PART_NUMBER) > 0 " );
                pstmt.setString ( 1, processType);
            }
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                     mktPartNumberList.add( new LabelValueBean( rs.getString( "P_MARKETING_PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not load Part Numbers", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return mktPartNumberList;
    }
   
    public static ArrayList loadPlanningReportDetails ( DBTransaction dbTransaction, String productId,String stepIds,String dateType,String category,String defaultPrompt) throws ScalarException
    {
         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList productDetaislList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL sp_planning_report_proc( ?, ?, ?, ?, ? ) }" );
             pstmt.setString( 1, productId );
             pstmt.setString( 2, stepIds );
             pstmt.setString( 3, dateType );
             pstmt.setString( 4, category );
             pstmt.setString( 5, defaultPrompt );

             rset    = pstmt.executeQuery();

             productDetaislList = ExcelUtils.getAllResultSetRows(rset, true);
             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Planning Report Details", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return productDetaislList;
    }
    public static int getProcessStepId ( DBTransaction dbTransaction, String partNumber, String psType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        int processStepId = 0;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "SELECT PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PART_NUMBER = ? AND PS.PS_TYPE = ?" );
            pstmt.setString( 1, partNumber );
            pstmt.setString( 2, psType );

            rset    = pstmt.executeQuery();

            if ( rset.next() )
            {
                processStepId = rset.getInt( "PROCESSSTEP_ID" );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get ProcessStepId", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return processStepId;
    }

    public static ArrayList<LabelValueBean> loadProcessList ( DBTransaction dbTransaction,String processTypeName ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> ProcessList    = null;

        try
        {
            ProcessList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "{ CALL sp_all_load_process_types( ? ) }" );
            pstmt.setString ( 1, processTypeName );
            ProcessList.add( new LabelValueBean(  "ALL PROCESS STEPS" ,  "0" ) );
            
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                 ProcessList.add( new LabelValueBean( rs.getString( "PS_TYPE" ), rs.getString( "PS_TYPE_NAME" ) ) );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not load Process Types", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return ProcessList;
    }
    public static ArrayList<ProductChange> getProductChangeList ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
        ResultSet rset                  = null;
        PreparedStatement pstmt         = null;
        ArrayList<ProductChange> productChangeList     = null;
        ProductChange productChange     = null;
        try
        {
            productChangeList = new ArrayList<ProductChange>();
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();                
            }

            //pstmt = conn.prepareStatement( "SELECT PCT.PROCESSSTEP_ID, P.PART_NUMBER, PS.PS_TYPE, PCT.FIELD_NAME, PCT.OLD_VALUE, PCT.NEW_VALUE, PCT.STATUS FROM PRODUCT_CHANGE_TABLE PCT, PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PCT.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND IS_ALERT_SENT = 1 ORDER BY PCT.TIMESTAMP ASC" );
            pstmt = conn.prepareStatement( "SELECT PCT.PROCESSSTEP_ID, P.PART_NUMBER, PS.PS_TYPE, PCT.FIELD_NAME, PCT.OLD_VALUE, PCT.NEW_VALUE, PCT.STATUS FROM PRODUCT_CHANGE_TABLE PCT LEFT OUTER JOIN PRODUCT P ON P.PROCESSSTEP_ID = PCT.PROCESSSTEP_ID\tLEFT OUTER JOIN PROCESS_STEP PS ON PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID WHERE IS_ALERT_SENT = 1 ORDER BY PCT.TIMESTAMP ASC" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                productChange = new ProductChange();
                productChange.setProcessStepId( rset.getInt ( "PROCESSSTEP_ID" ) );
                productChange.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                productChange.setPsType( rset.getString ( "PS_TYPE" ) );
                productChange.setFieldName( rset.getString ( "FIELD_NAME" ) );
                productChange.setOldValue( rset.getString  ( "OLD_VALUE" ) );
                productChange.setNewValue( rset.getString ( "NEW_VALUE" ) );
                productChange.setStatus( rset.getString ( "STATUS" ) );

                productChangeList.add ( productChange );
            }
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not load Product Change Info", sql );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return productChangeList;
    }


    public static ArrayList<LotDetails> getYieldNotificationList() throws ScalarException
    {
        Connection conn                 = null;
        ResultSet rset                  = null;
        PreparedStatement pstmt         = null;
        ArrayList<LotDetails> yieldNotificationList     = null;
        LotDetails lotDetail     = null;
        try
        {
        	yieldNotificationList = new ArrayList<LotDetails>();
            conn = DataSource.getConnection();                

            pstmt = conn.prepareStatement( "Select Part_number, Lot_number, Fab_lot_Number, Vendor_Name, Process_Type, CONVERT(varchar,Ship_Date,101) As Ship_date, Die_Qty, Ship_Qty FROM Notification_alert_table where Notification_Name = 'Yield Notification' and IS_User_Notified = 1 Order by Process_Type, Vendor_Name, Ship_Date" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
            	lotDetail = new LotDetails();
            	lotDetail.setPartNumber( rset.getString ( "Part_number" ) );
            	lotDetail.setLotNumber( rset.getString ( "Lot_number" ) );
            	lotDetail.setFabLotNumber( rset.getString ( "Fab_lot_Number" ) );
            	lotDetail.setVendorName( rset.getString ( "Vendor_Name" ) );
            	lotDetail.setProcessType( rset.getString  ( "Process_Type" ) );
            	lotDetail.setShipDate( rset.getString ( "Ship_date" ) );
            	lotDetail.setDieQtyIn( rset.getString( "Die_Qty" ) );
            	lotDetail.setShipDieQty( rset.getString( "Ship_Qty" ) );
            	
                yieldNotificationList.add ( lotDetail );
            }
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not load Yield Notification Info", sql );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return yieldNotificationList;
    }
    public static ArrayList<ProductChange> getLotChangeList ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
        ResultSet rset                  = null;
        PreparedStatement pstmt         = null;
        ArrayList<ProductChange> lotChangeList = new ArrayList<ProductChange>();
        ProductChange productChange     = null;
        try
        {

            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT LCT.LOT_ID, LT.LOT_NUMBER, LT.FAB_LOT_NUMBER, L_VENDOR_NAME, PS.PS_TYPE, LCT.FIELD_NAME, LCT.OLD_VALUE, LCT.NEW_VALUE, LCT.STATUS FROM LOT_CHANGE_TABLE LCT LEFT OUTER JOIN LOT_TABLE LT ON LT.LOT_ID = LCT.LOT_ID \n" +
                    "LEFT OUTER JOIN PROCESS_STEP PS ON PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID LEFT OUTER JOIN\n" +
                    "LOCATION L ON L.LOCATION_ID = LT.LOCATION_ID WHERE IS_ALERT_SENT = 1 ORDER BY LCT.TIMESTAMP ASC" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                productChange = new ProductChange();
                productChange.setLotId( rset.getInt ( "LOT_ID" ) );
                productChange.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                productChange.setFabLotNumber( rset.getString ( "FAB_LOT_NUMBER" ) );
                productChange.setVendorName( rset.getString ( "L_VENDOR_NAME" ) );
                productChange.setPsType( rset.getString ( "PS_TYPE" ) );
                productChange.setFieldName( rset.getString ( "FIELD_NAME" ) );
                productChange.setOldValue( rset.getString  ( "OLD_VALUE" ) );
                productChange.setNewValue( rset.getString ( "NEW_VALUE" ) );
                productChange.setStatus( rset.getString ( "STATUS" ) );

                lotChangeList.add ( productChange );
            }
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not load Lot Change Info", sql );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return lotChangeList;
    }

    public static ArrayList<ProductChange> getBomInvReOrderPartList ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
        ResultSet rset                  = null;
        PreparedStatement pstmt         = null;
        ArrayList<ProductChange> bomPartList = new ArrayList<ProductChange>();
        ProductChange productChange     = null;
        try
        {

            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT isNull(SUM(QTY_IN),0) as QTY_IN, isNull(Parameter_value, 0) AS REORDER_LEVEL_QTY, PART_NUMBER \n" +
                    "FROM LOT_TABLE LT, PRODUCT P , PROCESS_PARAMETER PP \n" +
                    "WHERE LT.PROCESSSTEP_ID = PP.PROCESSSTEP_ID AND PP.PROCESSSTEP_ID = P.PROCESSSTEP_ID \n" +
                    "AND parameter_key= 'Reorder Level' AND LOT_CURRENT_STATUS <> 'Close' AND P_TYPE = 'BOM' \n" +
                    "Group By Parameter_value, PART_NUMBER HAVING isNull(SUM(QTY_IN),0) < isNull(Parameter_value, 0)" );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                productChange = new ProductChange();
                productChange.setQtyIn( rset.getString ( "QTY_IN" ) );
                productChange.setReorderLevelQty( rset.getString ( "REORDER_LEVEL_QTY" ) );
                productChange.setPartNumber( rset.getString ( "PART_NUMBER" ) );

                bomPartList.add ( productChange );
            }
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not load BOM REORDER Level Info", sql );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return bomPartList;
    }

    public static void updateAlertStatusforProductChange ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
        PreparedStatement pstmt         = null;
        boolean success                 = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE PRODUCT_CHANGE_TABLE SET IS_ALERT_SENT = 0 WHERE IS_ALERT_SENT = 1" );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not update product change alert status", sql );
        }
        finally
        {
            if ( dbTransaction == null && success == false && conn != null)
            {
                try { conn.rollback(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
    }
    public static void updateAlertStatusforLotChange ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
        PreparedStatement pstmt         = null;
        boolean success                 = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE LOT_CHANGE_TABLE SET IS_ALERT_SENT = 0 WHERE IS_ALERT_SENT = 1" );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( SQLException sql)
        {
            throw new ScalarException( "Could not update lot change alert status", sql );
        }
        finally
        {
            if ( dbTransaction == null && success == false && conn != null)
            {
                try { conn.rollback(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
    }
    public static ArrayList loadProductionReportByMonthDetails ( DBTransaction dbTransaction, int month, int year ) throws ScalarException
    {
         Connection conn         = null;
         PreparedStatement pstmt = null;
         ResultSet rset          = null;
         boolean success         = false;

         ArrayList productionReportDetaislList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt   = conn.prepareStatement ( "{ CALL SP_PRODUCTION_REPORT_BY_MONTH( ?, ? ) }" );
             pstmt.setInt( 1, month );
             pstmt.setInt( 2, year );

             rset    = pstmt.executeQuery();

             productionReportDetaislList = ExcelUtils.getAllResultSetRows(rset, true);

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Production Report Details", e );
         }
         finally
         {
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return productionReportDetaislList;
    }
    public static Map<String, String> getTransactionReportDetails ( DBTransaction dbTransaction, int baseProductId, int month, int year ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;

        Map<String, String> transactionReportDetailsMap   = new HashMap<String, String>();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall ( "{ CALL SP_TRANSACTION_REPORT( ?, ?, ? ) }" );
            cstmt.setInt( 1, baseProductId );
            cstmt.setInt( 2, month );
            cstmt.setInt( 3, year );

            rset    = cstmt.executeQuery();

            if ( rset.next() )
            {
                transactionReportDetailsMap.put ( "DB_STARTING_WIP", rset.getInt( "DB_STARTING_WIP" )+"" );
                transactionReportDetailsMap.put ( "FAB_SHIPMENTS_WIP", rset.getInt( "FAB_SHIPMENTS_WIP" )+"" );
                transactionReportDetailsMap.put ( "DB_ENDING_WIP", rset.getInt( "DB_ENDING_WIP" )+"" );
                transactionReportDetailsMap.put ( "DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP", rset.getInt( "DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP" )+"" );
                transactionReportDetailsMap.put ( "ASM_STARTING_WIP", rset.getInt( "ASM_STARTING_WIP" )+"" );
                transactionReportDetailsMap.put ( "ASM_ENDING_WIP", rset.getInt( "ASM_ENDING_WIP" )+"" );
                transactionReportDetailsMap.put ( "ACTUAL_ASM_RECEIVINGS", rset.getInt( "ACTUAL_ASM_RECEIVINGS" )+"" );
                transactionReportDetailsMap.put ( "TOT_ASM_SHIPMENTS", rset.getInt( "TOT_ASM_SHIPMENTS" )+"" ); 
                transactionReportDetailsMap.put ( "ACTUAL_ASM_SHIPMENTS", rset.getInt( "ACTUAL_ASM_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "FT_STARTING_WIP", rset.getInt( "FT_STARTING_WIP" )+"" );
                transactionReportDetailsMap.put ( "ASM_SHIPMENTS_TO_FT", rset.getInt( "ASM_SHIPMENTS_TO_FT" )+"" );
                transactionReportDetailsMap.put ( "FT_ENDING_WIP", rset.getInt( "FT_ENDING_WIP" )+"" );
                transactionReportDetailsMap.put ( "ACTUAL_FT_RECEIVINGS", rset.getInt( "ACTUAL_FT_RECEIVINGS" )+"" );
                transactionReportDetailsMap.put ( "TOT_FT_SHIPMENTS", rset.getInt( "TOT_FT_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "FT_SHIPMENTS_TO_TR", rset.getInt( "FT_SHIPMENTS_TO_TR" )+"" );
                transactionReportDetailsMap.put ( "FT_SHIPMENTS_TO_FG", rset.getInt( "FT_SHIPMENTS_TO_FG" )+"" );
                transactionReportDetailsMap.put ( "ACTUAL_FT_SHIPMENTS", rset.getInt( "ACTUAL_FT_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "TR_STARTING_WIP", rset.getInt( "TR_STARTING_WIP" )+"" );
                transactionReportDetailsMap.put ( "FG_SHIPMENTS_TO_TR", rset.getInt( "FG_SHIPMENTS_TO_TR" )+"" );
                transactionReportDetailsMap.put ( "TR_ENDING_WIP", rset.getInt( "TR_ENDING_WIP" )+"" );
                transactionReportDetailsMap.put ( "TOT_TR_SHIPMENTS", rset.getInt( "TOT_TR_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "ACTUAL_TR_SHIPMENTS", rset.getInt( "ACTUAL_TR_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "FG_STARTING_WIP", rset.getInt( "FG_STARTING_WIP" )+"" );
                transactionReportDetailsMap.put ( "TR_SHIPMENTS_TO_FG", rset.getInt( "TR_SHIPMENTS_TO_FG" )+"" );
                transactionReportDetailsMap.put ( "FG_ENDING_WIP", rset.getInt( "FG_ENDING_WIP" )+"" );
                transactionReportDetailsMap.put ( "TOT_FG_SHIPMENTS", rset.getInt( "TOT_FG_SHIPMENTS" )+"" );
                transactionReportDetailsMap.put ( "SHIPMENTS_TO_CUSTOMERS", rset.getInt( "SHIPMENTS_TO_CUSTOMERS" )+"" );
                transactionReportDetailsMap.put ( "ACTUAL_FG_SHIPMENTS", rset.getInt( "ACTUAL_FG_SHIPMENTS" )+"" );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transaction Report Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return transactionReportDetailsMap;
    }
    public static ArrayList<TransactionDetail> getDetailedTransactionReportDetails ( DBTransaction dbTransaction, int baseProductId, String fromDate, String toDate, String processType, String vendorName ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;

        ArrayList<TransactionDetail> transactionReportDetails = new ArrayList<TransactionDetail>();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall ( "{ CALL SP_DETAILED_TRANSACTION_REPORT( ?, ?, ?, ?, ? ) }" );
            cstmt.setInt( 1, baseProductId );
            cstmt.setString( 2, fromDate );
            cstmt.setString( 3, toDate );
            cstmt.setString ( 4, processType );
            cstmt.setString ( 5, vendorName );

            rset    = cstmt.executeQuery();

            while ( rset.next() )
            {
                TransactionDetail transactionDetail = new TransactionDetail();
                transactionDetail.setPartNumber( rset.getString ( "PART_NUMBER" ) );
				transactionDetail.setLotNumber ( rset.getString ("Lot_Number") );
                transactionDetail.setStartingQty( rset.getInt ( "BEGINING_QTY"  ) );
                transactionDetail.setInQty( rset.getInt ( "IN_QTY" ) );
                transactionDetail.setOutQty( rset.getInt ( "OUT_QTY" ) );
                transactionDetail.setInFromFGQty( rset.getInt ( "IN_FROM_FG" ) );
                transactionDetail.setInFromTRQty( rset.getInt ( "IN_FROM_TR" ) );
                transactionDetail.setShipmentsToCustomer( rset.getInt ( "SHIPMENTS_TO_CUSTOMER" ) );
                transactionDetail.setScrapQty( rset.getInt ( "SCRAP_QTY" ) );
                transactionDetail.setEndingQty( rset.getInt ( "ENDING_QTY" ) );
                transactionDetail.setDieBankPartNumber( rset.getString ( "DB_PART_NUMBER" ) );

                transactionReportDetails.add ( transactionDetail );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transaction Report Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return transactionReportDetails;
    }
    public static ArrayList<TransactionDetail> getDetailedTransactionReportByLotDetails ( DBTransaction dbTransaction, int processStepId, String fromDate, String toDate, String processType, String vendorName ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;

        ArrayList<TransactionDetail> transactionReportDetails = new ArrayList<TransactionDetail>();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall ( "{ CALL SP_DETAILED_TRANSACTION_REPORT_BY_LOT( ?, ?, ?, ?, ? ) }" );
            cstmt.setInt( 1, processStepId );
            cstmt.setString( 2, fromDate );
            cstmt.setString( 3, toDate );
            cstmt.setString ( 4, processType );
            cstmt.setString ( 5, vendorName );

            rset    = cstmt.executeQuery();

            while ( rset.next() )
            {
                TransactionDetail transactionDetail = new TransactionDetail();
                transactionDetail.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                transactionDetail.setStartingQty( rset.getInt ( "BEGINING_QTY"  ) );
                transactionDetail.setInQty( rset.getInt ( "IN_QTY" ) );
                transactionDetail.setOutQty( rset.getInt ( "OUT_QTY" ) );
                transactionDetail.setInFromFGQty( rset.getInt ( "IN_FROM_FG" ) );
                transactionDetail.setInFromTRQty( rset.getInt ( "IN_FROM_TR" ) );
                transactionDetail.setShipmentsToCustomer( rset.getInt ( "SHIPMENTS_TO_CUSTOMER" ) );
                transactionDetail.setScrapQty( rset.getInt ( "SCRAP_QTY" ) );
                transactionDetail.setEndingQty( rset.getInt ( "ENDING_QTY" ) );
                transactionDetail.setDieBankPartNumber( rset.getString ( "DB_PART_NUMBER" ) );

                transactionReportDetails.add ( transactionDetail );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transaction Report By Lot Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return transactionReportDetails;
    }
    
    public static ArrayList<TransactionDetail> getDetailedTransactionReportDetails ( DBTransaction dbTransaction, DetailedTransactionReportForm detailedTransactionReportForm ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;

        ArrayList<TransactionDetail> transactionReportDetails = new ArrayList<TransactionDetail>();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall ( "{ CALL SP_DETAILED_TRANSACTION_REPORT_BY_PRODUCT( ?, ?, ?, ?, ?, ?, ? ) }" );
            cstmt.setInt( 1, detailedTransactionReportForm.getBaseProductId() );
            cstmt.setString( 2, detailedTransactionReportForm.getFromDate() );
            cstmt.setString( 3, detailedTransactionReportForm.getToDate() );
            cstmt.setString ( 4, detailedTransactionReportForm.getProcessType() );
            cstmt.setString ( 5, detailedTransactionReportForm.getVendorName() );
            cstmt.setInt ( 6, detailedTransactionReportForm.getProcessStepId() );
            cstmt.setInt ( 7, detailedTransactionReportForm.isByLot() ? 1 : 0 );
            
           /* System.out.println("1	:	"+ detailedTransactionReportForm.getBaseProductId() );
            System.out.println("2	:	"+ detailedTransactionReportForm.getFromDate() );
            System.out.println("3	:	"+ detailedTransactionReportForm.getToDate() );
            System.out.println("4	:	"+ detailedTransactionReportForm.getProcessType() );
            System.out.println("5	:	"+ detailedTransactionReportForm.getVendorName() );
            System.out.println("6	:	"+ detailedTransactionReportForm.getProcessStepId() );
            System.out.println("7	:	"+ detailedTransactionReportForm.isByLot() );*/

            rset    = cstmt.executeQuery();

            while ( rset.next() )
            {
                TransactionDetail transactionDetail = new TransactionDetail();
                transactionDetail.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                if ( detailedTransactionReportForm.isByLot() )
                {
                    transactionDetail.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                }   
                transactionDetail.setStartingQty( rset.getInt ( "BEGINING_QTY"  ) );
                transactionDetail.setInQty( rset.getInt ( "IN_QTY" ) );
                transactionDetail.setOutQty( rset.getInt ( "OUT_QTY" ) );
                transactionDetail.setInFromFGQty( rset.getInt ( "IN_FROM_FG" ) );
                transactionDetail.setInFromTRQty( rset.getInt ( "IN_FROM_TR" ) );
                transactionDetail.setShipmentsToCustomer( rset.getInt ( "SHIPMENTS_TO_CUSTOMER" ) );
                transactionDetail.setScrapQty( rset.getInt ( "SCRAP_QTY" ) );
                transactionDetail.setEndingQty( rset.getInt ( "ENDING_QTY" ) );
                transactionDetail.setInFromRMA( rset.getInt ( "IN_FROM_RMA" ) );
                transactionDetail.setTransferToBulk( rset.getInt( "TRANSFER_TO_BULK" ) );
                transactionDetail.setInFromOther( rset.getInt ( "IN_FROM_OTHER" ) );
                transactionDetail.setDieBankPartNumber( rset.getString ( "DB_PART_NUMBER" ) );
                //transactionDetail.setUnitStdCostIn( rset.getDouble( "UNIT_STD_COST_IN" ) );
                //transactionDetail.setUnitStdValueAdd( rset.getDouble( "UNIT_STD_VALUE_ADD" ) );
                //transactionDetail.setUnitStdCostOut( rset.getDouble( "UNIT_STD_COST_OUT" ) );
                //transactionDetail.setUnitStdValueAddEprom( rset.getDouble( "UNIT_STD_VALUE_ADD_EPROM" ) );
                //transactionDetail.setUnitStdValueAddBG( rset.getDouble( "UNIT_STD_VALUE_ADD_BG" ) );
                //transactionDetail.setUnitStdValueDieFrieght( rset.getDouble( "UNIT_STD_VALUE_DIE_FRIEGHT" ) );
                //transactionDetail.setUnitStdValueAssyFrieght( rset.getDouble( "UNIT_STD_VALUE_ASSY_FRIEGHT" ) );
                //transactionDetail.setStdYield( rset.getDouble( "STD_YIELD" ) );

                transactionReportDetails.add ( transactionDetail );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Transaction Report Details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return transactionReportDetails;
    }

	public static String getProcessTypeByProcessStep_ID(int processStepId) throws ScalarException 
	{
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String processType 		= null;
        try
        {
        	conn    = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE ProcessStep_ID = ?");
            pstmt.setInt( 1 , processStepId );
            rs = pstmt.executeQuery();
            
            if (rs.next())
            {
            	processType = rs.getString( "PS_TYPE_NAME" );
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load all Process's information ", e );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
		return processType;
	}

    public static ArrayList<LabelValueBean> getPartNumbersByProcessParameter(DBTransaction dbTransaction, String psTypeName, String processParameterName, String processParameterValue) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String processType = null;
        ArrayList<LabelValueBean> list = new ArrayList<LabelValueBean>();
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement("select P.PART_NUMBER, P.PROCESSSTEP_ID FROM PROCESS_STEP PS, PRODUCT P, PROCESS_PARAMETER PP WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PP.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = ? AND PP.PARAMETER_KEY = ? AND PP.PARAMETER_VALUE = ?");
            pstmt.setString(1, psTypeName);
            pstmt.setString(2, processParameterName);
            pstmt.setString(3, processParameterValue);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                list.add(new LabelValueBean(rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID")));
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not load part numbers by process parameter", e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException igonore) {
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException igonore) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException igonore) {
                }
            }
        }
        return list;
    }
}

